import config
import os
import sqlite3
import hashlib
from enum import AEnums
import re
import DriveUtil
from mixins import pathMixin

class SQLLite(pathMixin):
    sql = None

    def __init__(self):
        try:
            from win32com.shell import shellcon, shell
            self.homedir = shell.SHGetFolderPath(0, shellcon.CSIDL_APPDATA, 0, 0)
        except ImportError: # quick semi-nasty fallback for non-windows/win32com case
            self.homedir = os.path.expanduser("~")
        self.homedir += '/AnyBackup'
        if not self.sql:
            if os.path.exists('%s/anybackup.db'%self.homedir):
                self.sql = sqlite3.connect('%s/anybackup.db'%self.homedir,check_same_thread = False)
            else:
                self.sql = sqlite3.connect('%s/anybackup.db'%self.homedir,check_same_thread = False)
                c = self.sql.cursor()
                c.execute('''CREATE TABLE {0} ({1} text, {2} text)'''.format(AEnums.VALIDEXTS,AEnums.EXT,AEnums.SETNAME))
                c.execute('''CREATE TABLE {0} ({1} text, {2} text)'''.format(AEnums.SKIPLIST,AEnums.SKIP,AEnums.SETNAME))
                c.execute('''CREATE TABLE {0} ({1} text, {2} text, {3} text, {4} real, {5} real,
                            {6} text, {7} text, {8} text, {9} text, {10} integer, {11} text,
                            PRIMARY KEY ({1}))'''.format(
                                AEnums.FILES,AEnums.ID,AEnums.NAME,AEnums.PATH,
                                AEnums.MTIME,AEnums.SIZE,AEnums.SERIAL,
                                AEnums.VOLNAME,AEnums.PARENT,AEnums.PREFIX,AEnums.DIR,
                                AEnums.SETNAME))
                c.execute('''CREATE TABLE {0} ({1} text, {2} text, {3} text,
                            {4} real, {5} real, {6} text, {7} text, {8} text,
                            PRIMARY KEY ({1}, {2}, {6}))'''.format(AEnums.DRIVES,AEnums.NAME,
                                   AEnums.SERIAL,AEnums.TYPE,AEnums.FREESPACE,AEnums.TOTALSPACE,
                                   AEnums.SETNAME,AEnums.REMOTEHOST,AEnums.REMOTEPATH))
                c.execute('''CREATE TABLE {0} ({1} text, {2} text, {3} text,
                          PRIMARY KEY ({1},{2},{3}))'''.format(
                            AEnums.LOCKED,AEnums.NAME,AEnums.SERIAL,AEnums.SETNAME))
                c.execute('''CREATE TABLE {0} ({1} text, {2} text, {3} text, {4} text,
                            PRIMARY KEY ({1},{2},{3},{4}))'''.format(
                    AEnums.PATHS, AEnums.PATH, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME
                ))
                c.execute('''CREATE TABLE {0} ({1} text, {2} text, {3} text, {4} text,
                        PRIMARY KEY ({1}))'''.format(
                    AEnums.ROOT, AEnums.ID, AEnums.NAME, AEnums.SERIAL, AEnums.SETNAME,
                ))
                c.execute('''CREATE TABLE {0} ({1} text, PRIMARY KEY ({1}))'''.format(
                    AEnums.SETS,AEnums.SETNAME
                ))
                c.execute('''INSERT INTO {0} VALUES (?)'''.format(
                    AEnums.SETS),['AnyBackup']
                )
                c.execute('''PRAGMA journal_mode=OFF''')
                c.execute('''PRAGMA synchronous=OFF''')
                c.execute('''PRAGMA temp_store=2''')
                c.execute('''PRAGMA count_changes=OFF''')
                self.sql.commit()

    def addValidExt(self,ext,set):
        c = self.sql.cursor()
        c.execute('''INSERT INTO {0} VALUES (?,?)'''.format(
            AEnums.VALIDEXTS,AEnums.SETNAME),[ext,set])
        self.sql.commit()

    def setValidExt(self,ext,set):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.VALIDEXTS,AEnums.SETNAME),[set]
        )
        if type(ext) != str:
            lst = ext
        else:
            lst = [ext]
        for ext in lst:
            c.execute('''INSERT INTO {0} VALUES (?,?)'''.format(AEnums.VALIDEXTS),
                [ext,set])
        self.sql.commit()

    def addSkip(self,skip,set):
        c = self.sql.cursor()
        c.execute('''INSERT INTO {0} VALUES (?,?)'''.format(AEnums.SKIPLIST),[skip,set])
        self.sql.commit()

    def setSkip(self,skip,set):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.SKIPLIST,AEnums.SETNAME),[set]
        )
        if type(skip) != str:
            lst = skip
        else:
            lst = [skip]
        for skip in lst:
            c.execute('''INSERT INTO {0} VALUES (?,?)'''.format(AEnums.SKIPLIST),
                [skip,set])
        self.sql.commit()

    def getSets(self):
        c = self.sql.cursor()
        c.execute('''SELECT {0} FROM {1}'''.format(
            AEnums.SETNAME,AEnums.SETS,
        ))
        return [x[0] for x in c.fetchall()]

    def addSet(self,set):
        c = self.sql.cursor()
        c.execute('''INSERT INTO {0} VALUES (?)'''.format(
            AEnums.SETS),[set]
        )
        self.sql.commit()

    def removeSet(self,set):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.SETS,AEnums.SETNAME),[set],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.FILES,AEnums.SETNAME),[set],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.DRIVES,AEnums.SETNAME),[set],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.VALIDEXTS,AEnums.SETNAME),[set],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.SKIPLIST,AEnums.SETNAME),[set],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.LOCKED,AEnums.SETNAME),[set],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.ROOT,AEnums.SETNAME),[set],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=?'''.format(
            AEnums.PATHS,AEnums.SETNAME),[set],
        )
        self.sql.commit()

    def getValidExt(self,set):
        c = self.sql.cursor()
        c.execute('''SELECT {0} FROM {1} WHERE {2}=?'''.format(
            AEnums.EXT,AEnums.VALIDEXTS,AEnums.SETNAME),
            [set],
        )
        return [x[0] for x in c.fetchall()]

    def getSkipList(self,set):
        c = self.sql.cursor()
        c.execute('''SELECT {0} FROM {1} WHERE {2}=?'''.format(
            AEnums.SKIP,AEnums.SKIPLIST,AEnums.SETNAME),
            [set],
        )
        return [x[0] for x in c.fetchall()]

    def getDrive(self,name,serial,set):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.DRIVES,AEnums.NAME,AEnums.SERIAL,AEnums.SETNAME),[name,serial,set])
        ret = c.fetchone()
        if ret:
            return {
                AEnums.NAME : ret[0],
                AEnums.SERIAL : ret[1],
                AEnums.TYPE : ret[2],
                AEnums.FREESPACE : ret[3],
                AEnums.TOTALSPACE : ret[4],
                AEnums.SETNAME : ret[5],
                AEnums.REMOTEHOST : ret[6],
                AEnums.REMOTEPATH : ret[7],
            }
        return None

    def getDriveByFile(self,fileRef):
        return self.getDrive(fileRef[AEnums.VOLNAME],
                             fileRef[AEnums.SERIAL],
                             fileRef[AEnums.SETNAME])

    def updateDrive(self,dd):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.DRIVES,AEnums.NAME,AEnums.SERIAL,AEnums.SETNAME),
            [dd[AEnums.NAME],dd[AEnums.SERIAL],dd[AEnums.SETNAME]],
        )
        c.execute('''INSERT INTO {0} VALUES (?,?,?,?,?,?,?,?)'''.format(
            AEnums.DRIVES),
            [dd[AEnums.NAME],dd[AEnums.SERIAL],dd[AEnums.TYPE],
             dd[AEnums.FREESPACE],dd[AEnums.TOTALSPACE],dd[AEnums.SETNAME],
             dd.get(AEnums.REMOTEHOST,''),dd.get(AEnums.REMOTEPATH,''),]
        )
        self.sql.commit()

    def removeFiles(self,drives):
        c = self.sql.cursor()
        for drive in drives:
            c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
                AEnums.FILES,AEnums.VOLNAME,AEnums.SERIAL,AEnums.SETNAME),
                [drive[AEnums.NAME],drive[AEnums.SERIAL],drive[AEnums.SETNAME]
            ])
            c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
                AEnums.ROOT,AEnums.NAME,AEnums.SERIAL,AEnums.SETNAME),
                [drive[AEnums.NAME],drive[AEnums.SERIAL],drive[AEnums.SETNAME]
            ])
        self.sql.commit()

    def updateFreeSpace(self,d):
        letter = DriveUtil.getLetter(d[AEnums.NAME],d[AEnums.SERIAL])
        if not letter:
            #print "Cannot update freespace for " + self._name + " when drive is not connected!\n"
            return False
        freeSpace = DriveUtil.getFreeSpace(letter)
        totalSpace = DriveUtil.getTotalSpace(letter)
        d[AEnums.FREESPACE] = freeSpace
        d[AEnums.TOTALSPACE] = totalSpace
        return self.updateDrive(d)

    def getDriveFiles(self,d):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.FILES,AEnums.VOLNAME,AEnums.SERIAL,AEnums.SETNAME),
            [d[AEnums.NAME],d[AEnums.SERIAL],d[AEnums.SETNAME]])
        raw = c.fetchall()
        return self.convertFiles(raw)

    def isLocked(self,drive):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.LOCKED,AEnums.NAME,AEnums.SERIAL,AEnums.SETNAME),
            [drive[AEnums.NAME],drive[AEnums.SERIAL],drive[AEnums.SETNAME]
        ])
        return bool(c.fetchall())

    def findDir(self,drive,path):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=? AND {3}=? AND {3} LIKE ?'''.format(
            AEnums.FILES,AEnums.VOLNAME,AEnums.SERIAL,AEnums.SETNAME,AEnums.PATH),
            [drive[AEnums.NAME],drive[AEnums.SETNAME],drive[AEnums.SERIAL],path+'%',
        ])
        return bool(c.fetchall())

    def getFile(self,file,set):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=?'''.format(
            AEnums.FILES,AEnums.ID,AEnums.SETNAME),[file,set
        ])
        file = c.fetchone()
        if file:
            return {
                AEnums.ID : file[0],
                AEnums.NAME : file[1],
                AEnums.PATH : file[2],
                AEnums.MTIME : file[3],
                AEnums.SIZE : file[4],
                AEnums.SERIAL : file[5],
                AEnums.VOLNAME : file[6],
                AEnums.PARENT : file[7],
                AEnums.PREFIX : file[8],
                AEnums.DIR : bool(file[9]),
                AEnums.SETNAME : file[10],
            }

    def getFullPath(self,fileRef):
        letter = DriveUtil.getLetter(fileRef[AEnums.VOLNAME],fileRef[AEnums.SERIAL])
        if not letter:
            return None
        return self.createPath(letter+':',fileRef[AEnums.PATH],fileRef[AEnums.NAME])

    def getPaths(self,drive):
        c = self.sql.cursor()
        c.execute('''SELECT {0} from {1} WHERE {2}=? AND {3}=? AND {4}=?'''.format(
            AEnums.PATH,AEnums.PATHS,AEnums.NAME,AEnums.SERIAL,AEnums.SETNAME),
            [drive[AEnums.NAME],drive[AEnums.SERIAL],drive[AEnums.SETNAME]
        ])
        ret = c.fetchall()
        return [x[0] for x in ret]

    def addPath(self,drive,path):
        returnCode = True
        letter = DriveUtil.getLetter(drive[AEnums.NAME],drive[AEnums.SERIAL])
        newPath = re.sub(r'^%s:'%letter,'',path)
        if not re.search(r'%s$'%re.escape(os.sep),newPath,re.IGNORECASE):
                newPath += os.sep
        result = self.containsPath(drive,newPath)
        if result == AEnums.OVERWRITE:
            self.containsPath(drive,newPath,overwrite=True)
            returnCode = AEnums.OVERWRITE
        elif result:
            return False
        c = self.sql.cursor()
        c.execute('''INSERT INTO {0} VALUES (?,?,?,?)'''.format(
            AEnums.PATHS),
            [newPath,drive[AEnums.NAME],drive[AEnums.SERIAL],drive[AEnums.SETNAME]]
        )
        self.sql.commit()
        return returnCode

    def containsPath(self,drive,path,overwrite=False):
        for directory in self.getPaths(drive):
            oldPath = directory
            if self._containsPath(oldPath,path):
                return True
            elif self._containsPath(path,oldPath):
                if overwrite:
                    self.removePath(drive,oldPath)
                return AEnums.OVERWRITE
        return False

    def _containsPath(self,dir,subdir):
        if dir.endswith(os.sep):
            dir = dir[0:-1]
        if subdir.endswith(os.sep):
            subdir = subdir[0:-1]
        head, tail = os.path.split(subdir)
        while head and tail:
            if head == dir:
                return True
            head, tail = os.path.split(head)
        else:
            return False

    def removePath(self,drive,path):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=? AND {4}=?'''.format(
            AEnums.PATHS,AEnums.NAME,AEnums.SERIAL,AEnums.PATH,AEnums.SETNAME),
            [drive[AEnums.NAME],drive[AEnums.SERIAL],path,drive[AEnums.SETNAME]],
        )
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=? AND {4} LIKE ?'''.format(
            AEnums.FILES,AEnums.NAME,AEnums.SERIAL,AEnums.SETNAME,AEnums.PATH),
            [drive[AEnums.NAME],drive[AEnums.SERIAL],drive[AEnums.SETNAME],path+'%'],
        )

    def setRoot(self,drive,rootFiles):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.ROOT,AEnums.NAME,AEnums.SERIAL,AEnums.SETNAME),
            [drive[AEnums.NAME],drive[AEnums.SERIAL],drive[AEnums.SETNAME],
        ])
        for file in rootFiles:
            c.execute('''INSERT INTO {0} VALUES (?,?,?,?)'''.format(
                AEnums.ROOT),[file,drive[AEnums.NAME],drive[AEnums.SERIAL],drive[AEnums.SETNAME]
            ])
        self.sql.commit()

    def getFiles(self,name,set):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=? and {2}=?'''.format(
            AEnums.FILES,AEnums.PARENT,AEnums.SETNAME),[name,set
        ])
        ret = c.fetchall()
        ret = self.convertFiles(ret)
        return ret

    def getRootFile(self,drive):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1} in (SELECT {1} FROM {2} WHERE {3}=? AND {4}=?)'''.format(
            AEnums.FILES,AEnums.ID,AEnums.ROOT,AEnums.NAME,AEnums.SERIAL),[drive[AEnums.NAME],drive[AEnums.SERIAL]],
        )
        return self.convertFiles(c.fetchall())

    def addDrive(self,dd,paths=None):
        c = self.sql.cursor()
        if AEnums.FREESPACE not in dd:
            dd[AEnums.FREESPACE] = 1
        if AEnums.TOTALSPACE not in dd:
            dd[AEnums.TOTALSPACE] = 1
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.PATHS,AEnums.NAME,AEnums.SERIAL,AEnums.SETNAME),
            [dd[AEnums.NAME],dd[AEnums.SERIAL],dd[AEnums.SETNAME],
        ])
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? and {3}=?'''.format(
            AEnums.ROOT,AEnums.NAME,AEnums.SERIAL,AEnums.SETNAME),
            [dd[AEnums.NAME],dd[AEnums.SERIAL],dd[AEnums.SETNAME],
        ])
        if not paths:
            paths = [os.sep]
        c.execute('''INSERT INTO {0} VALUES (?,?,?,?,?,?,?,?)'''.format(
            AEnums.DRIVES),
            [dd[AEnums.NAME],dd[AEnums.SERIAL],dd[AEnums.TYPE],
             dd[AEnums.FREESPACE],dd[AEnums.TOTALSPACE],dd[AEnums.SETNAME],
             dd.get(AEnums.REMOTEHOST,''),dd.get(AEnums.REMOTEPATH,''),
        ])
        for path in paths:
            c.execute('''INSERT INTO {0} VALUES (?,?,?,?)'''.format(
                AEnums.PATHS),
                [path,dd[AEnums.NAME],dd[AEnums.SERIAL],dd[AEnums.SETNAME]
            ])
        return self.sql.commit()

    def addFile(self,drive,fd,root=False):
        c = self.sql.cursor()
        c.execute('''INSERT INTO {0} VALUES (?,?,?,?,?,?,?,?,?,?,?)'''.format(
            AEnums.FILES),
            [fd[AEnums.ID],fd[AEnums.NAME],fd[AEnums.PATH],
             fd[AEnums.MTIME],fd[AEnums.SIZE],fd[AEnums.SERIAL],
             fd[AEnums.VOLNAME],fd[AEnums.PARENT],
             fd[AEnums.PREFIX],int(fd[AEnums.DIR]),fd[AEnums.SETNAME],
        ])
        if root:
            c.execute('''INSERT INTO {0} VALUES (?,?,?,?)'''.format(
                AEnums.ROOT),
                [fd[AEnums.ID],drive[AEnums.NAME],
                 drive[AEnums.SERIAL],fd[AEnums.SETNAME],
            ])

    def commit(self):
        self.sql.commit()
        
    def removeDrive(self,drive):
        c = self.sql.cursor()
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.DRIVES,AEnums.NAME,AEnums.SERIAL,AEnums.SETNAME),
            [drive[AEnums.NAME],drive[AEnums.SERIAL],drive[AEnums.SETNAME]
        ])
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.PATHS,AEnums.NAME,AEnums.SERIAL,AEnums.SETNAME),
            [drive[AEnums.NAME],drive[AEnums.SERIAL],drive[AEnums.SETNAME]
        ])
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.ROOT,AEnums.NAME,AEnums.SERIAL,AEnums.SETNAME),
            [drive[AEnums.NAME],drive[AEnums.SERIAL],drive[AEnums.SETNAME]
        ])
        c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
            AEnums.FILES,AEnums.VOLNAME,AEnums.SERIAL,AEnums.SETNAME),
            [drive[AEnums.NAME],drive[AEnums.SERIAL],drive[AEnums.SETNAME]
        ])
        return self.sql.commit()

    def search(self,regex,set):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2} LIKE ?'''.format(
            AEnums.FILES,AEnums.SETNAME,AEnums.NAME),[set,regex,
        ])
        ret = c.fetchall()
        return self.convertFiles(ret)

    def convertFiles(self,ret):
        files = []
        for file in ret:
            files.append({
                AEnums.ID : file[0],
                AEnums.NAME : file[1],
                AEnums.PATH : file[2],
                AEnums.MTIME : file[3],
                AEnums.SIZE : file[4],
                AEnums.SERIAL : file[5],
                AEnums.VOLNAME : file[6],
                AEnums.PARENT : file[7],
                AEnums.PREFIX : file[8],
                AEnums.DIR : bool(file[9]),
                AEnums.SETNAME : file[10],
            })
        return files

    def removeFile(self,name,set):
        files = self.getFiles(name,set)
        c = self.sql.cursor()
        for file in files:
            c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=?'''.format(
                AEnums.FILES, AEnums.ID,AEnums.SETNAME),
                [file[AEnums.ID],file[AEnums.SETNAME],
            ])
            c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=?'''.format(
                AEnums.ROOT, AEnums.ID,AEnums.SETNAME),
                [file[AEnums.ID],file[AEnums.SETNAME],
            ])
        return self.sql.commit()

    def lockDrives(self,drives,isLock):
        c = self.sql.cursor()
        for drive in drives:
            c.execute('''DELETE FROM {0} WHERE {1}=? AND {2}=? AND {3}=?'''.format(
                AEnums.LOCKED,AEnums.NAME,AEnums.SERIAL,AEnums.SETNAME),
                [drive[AEnums.NAME],drive[AEnums.SERIAL],drive[AEnums.SETNAME],
            ])
            if isLock:
                c.execute('''INSERT INTO {0} VALUES (?,?,?)'''.format(
                    AEnums.LOCKED),
                    [drive[AEnums.NAME],drive[AEnums.SERIAL],drive[AEnums.SETNAME],
                ])
        return self.sql.commit()

    def getDrives(self,type,set):
        c = self.sql.cursor()
        c.execute('''SELECT * FROM {0} WHERE {1}=? AND {2}=?'''.format(
            AEnums.DRIVES,AEnums.TYPE,AEnums.SETNAME),
            [type,set])
        ret = c.fetchall()
        drives = []
        for drive in ret:
            drives.append({
                AEnums.NAME : drive[0],
                AEnums.SERIAL : drive[1],
                AEnums.TYPE : drive[2],
                AEnums.FREESPACE : drive[3],
                AEnums.TOTALSPACE : drive[4],
                AEnums.SETNAME : drive[5],
                AEnums.REMOTEHOST : drive[6],
                AEnums.REMOTEPATH : drive[7],
            })
        return drives